import { Injectable, Logger } from '@nestjs/common';
import { createConnection } from 'mysql2/promise';
import { ConfigService } from '@nestjs/config';

@Injectable()
export class DatabaseInitializerService {
  private readonly logger = new Logger(DatabaseInitializerService.name);

  constructor(private readonly configService: ConfigService) {}

  /**
   * 获取数据库连接配置
   */
  private getConnectionConfigWithoutDatabase() {
    return {
      host: this.configService.get<string>('DB_HOST', '192.168.125.10'),
      port: this.configService.get<number>('DB_PORT', 3306),
      user: this.configService.get<string>('DB_USER', 'root'),
      password: this.configService.get<string>('DB_PASS', 'password'),
    };
  }

  /**
   * 获取目标数据库名称
   */
  private getTargetDatabaseName() {
    return this.configService.get<string>('DB_NAME', 'recipedatabase');
  }

  /**
   * 确保数据库存在（如果不存在则创建）
   */
  async ensureDatabaseExists() {
    const { host, port, user, password } =
      this.getConnectionConfigWithoutDatabase();
    const database = this.getTargetDatabaseName();

    this.logger.log(`Connecting to MySQL server at ${host}:${port}...`);
    const connection = await createConnection({ host, port, user, password });

    this.logger.log(`Checking if database "${database}" exists...`);
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\``);
    this.logger.log(`Database "${database}" ensured.`);

    await connection.end();
  }

  /**
   * 执行自定义 SQL（比如建表语句、初始化数据）。需指定目标数据库。
   * @param sql 要执行的 SQL 语句
   */
  async executeSql(sql: string) {
    const { host, port, user, password } =
      this.getConnectionConfigWithoutDatabase();
    const database = this.getTargetDatabaseName();

    this.logger.log(`🔌 Connecting to MySQL (with DB: ${database})...`);
    const connection = await createConnection({
      host,
      port,
      user,
      password,
      database, // ✅ 这次连接的是具体的数据库
      multipleStatements: true, // ✅ 允许一次执行多个 SQL 语句（用 ; 分隔）
    });

    this.logger.log(`🚀 Executing SQL: ${sql.substring(0, 50)}...`);
    await connection.query(sql);
    this.logger.log(`✅ SQL executed successfully.`);

    await connection.end();
  }
}
